﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.IO;
using System.Net;
using Pipes;
using Pipes.Xml;
using Pipes.Aras;

namespace Pipes
{
  public class TestBed
  {
    public static void Test()
    {
      var reader = new IO.FileTextSource(@"C:\Users\edomke\Commodity Site (05-08-2013).kml").Pipe(new Xml.XmlTextReader());
      var folderNumber = -1;
      var headerNodes = new List<IXmlNode>();
      var writers = new List<StringWriter>();
      var xmlWriters = new List<Sgml.ISgmlTextWriter>();
      writers.Add(new StringWriter());
      var settings = new Xml.XmlWriterSettings();
      settings.ConformanceLevel = System.Xml.ConformanceLevel.Fragment;
      settings.OmitXmlDeclaration = true;
      xmlWriters.Add(Xml.XmlTextWriter.Create(writers.Last(), settings));
      var currWriter = xmlWriters.Last();

      foreach (var node in reader)
      {
        if (node.Name.LocalName == "Folder" && node.Type == XmlNodeType.Element)
        {
          folderNumber += 1;
          if (folderNumber > 0) 
          {
            writers.Add(new StringWriter());
            xmlWriters.Add(Xml.XmlTextWriter.Create(writers.Last(), settings));
            currWriter = xmlWriters.Last();
            foreach (var headerNode in headerNodes)
            {
              currWriter.Node(headerNode);
            }
          }
        }

        if (currWriter == null)
        {
          foreach (var xmlWriter in xmlWriters)
          {
            xmlWriter.Node(node);
          }
        }
        else
        {
          currWriter.Node(node);
          if (folderNumber < 0) headerNodes.Add(node);
        }

        if (node.Name.LocalName == "Folder" && node.Type == XmlNodeType.EndElement) currWriter = null;
      }

      foreach (var xmlWriter in xmlWriters.OfType<IDisposable>())
      {
        xmlWriter.Dispose();
      }

      foreach (var writer in writers)
      {
        Debug.Print(writer.ToString());
        writer.Dispose();
      }
    }

    //public static void AmlTest()
    //{
    //  var aml = @"<SOAP-ENV:Envelope xmlns:SOAP-ENV=""http://schemas.xmlsoap.org/soap/envelope/""> <SOAP-ENV:Body> <Result> <Item type=""Concern"" typeId=""F1D2DE3EAFAB4B2B935D7061C49DF35B"" id=""CE792EFD900A42A4A4B0EF5BF2FA8AB7""> <classification>Production/Production Line</classification> <id keyed_name=""C:059303"" type=""Concern"">CE792EFD900A42A4A4B0EF5BF2FA8AB7</id> <mfg_line keyed_name=""OEC500-2"" type=""Manufacturing Entity""> <Item type=""Manufacturing Entity"" typeId=""A46CE867356D43C8A3BC7FFC83FC97E0"" id=""2A82E407803C414582325950B686EBE0""> <id keyed_name=""OEC500-2"" type=""Manufacturing Entity"">2A82E407803C414582325950B686EBE0</id> <itemtype>A46CE867356D43C8A3BC7FFC83FC97E0</itemtype> </Item> </mfg_line> <item_nbr_base keyed_name=""348-0534-000"" type=""Part"">C0F27B3A061346BDB2765F78C66450F4</item_nbr_base> </Item> <Item type=""Concern"" typeId=""F1D2DE3EAFAB4B2B935D7061C49DF35B"" id=""16351507B0CD4DFA98F7AE863F471FA5""> <classification>Production/Production Line</classification> <id keyed_name=""C:059304"" type=""Concern"">16351507B0CD4DFA98F7AE863F471FA5</id> <mfg_line keyed_name=""SA006"" type=""Manufacturing Entity""> <Item type=""Manufacturing Entity"" typeId=""A46CE867356D43C8A3BC7FFC83FC97E0"" id=""BA1A844C01B74903954781232D5980DA""> <id keyed_name=""SA006"" type=""Manufacturing Entity"">BA1A844C01B74903954781232D5980DA</id> <itemtype>A46CE867356D43C8A3BC7FFC83FC97E0</itemtype> </Item> </mfg_line> <item_nbr_base keyed_name=""605-5076-000"" type=""Part"">338283DC3371458F905E330976C77AB8</item_nbr_base> </Item> <Item type=""Concern"" typeId=""F1D2DE3EAFAB4B2B935D7061C49DF35B"" id=""E575F63FFA7746419D52134C3B0A5C4D""> <classification>Production/Production Line</classification> <id keyed_name=""C:059305"" type=""Concern"">E575F63FFA7746419D52134C3B0A5C4D</id> <mfg_line keyed_name=""FA025"" type=""Manufacturing Entity""> <Item type=""Manufacturing Entity"" typeId=""A46CE867356D43C8A3BC7FFC83FC97E0"" id=""20D641B68BC4490B9ABD10D21845FEFD""> <id keyed_name=""FA025"" type=""Manufacturing Entity"">20D641B68BC4490B9ABD10D21845FEFD</id> <itemtype>A46CE867356D43C8A3BC7FFC83FC97E0</itemtype> </Item> </mfg_line> <item_nbr_base keyed_name=""905-3678-001"" type=""Part"">70255F00F1A943A7BC7B95354E9D531B</item_nbr_base> </Item> </Result> </SOAP-ENV:Body> </SOAP-ENV:Envelope>";
    //  var reader = new IO.StringTextSource(aml).Pipe(new Xml.XmlTextReader()).Pipe(new Aras.ItemXmlReader());
    //  foreach (var item in reader)
    //  {
    //    //Debug.Print("type = {0}, id = {1}", item.Attribute("type"), item.Attribute("id"));
    //  }
    //}

    public static void ExcelTest()
    {
      var reader = new IO.FileStreamSource(@"C:\Users\edomke\export.xls")
                    .Pipe(new Spreadsheet.ExcelTableReader()).SetSheet("Export Worksheet").SetRange(1, 1, 3, 41174);
      reader.FirstRowColumnNames = true;

      using (var streamWriter = new StreamWriter(@"C:\query.sql"))
      {
        var writer = new Aras.ArasSqlTextWriter(streamWriter);
        writer.Line("declare @concern_update table (");
        writer.Line("  concern_id int null, ");
        writer.Line("  date_assigned [datetime] null, ");
        writer.Line("  date_closed [datetime] null ");
        writer.Line(");");
        foreach (var row in reader)
        {
          writer.Raw("insert into @concern_update values (").NumberValue(row.Item("CONCERN_ID"))
            .Raw(",").DateValue(row.Item("DATE_ASSIGNED"))
            .Raw(",").DateValue(row.Item("DATE_CLOSED")).Raw(");").Line();
        }
        writer.Flush();
      }
    }

    public static void HtmlWriter()
    {
      var stringWriter = new StringWriter();
      var settings = new Sgml.HtmlWriterSettings();
      settings.ReplaceConsecutiveSpaceNonBreaking = true;
      //var settings = new Xml.XmlWriterSettings();
      //settings.OmitXmlDeclaration = true;
      settings.Indent = true;
      var writer = Sgml.HtmlTextWriter.Create(stringWriter, settings);
      //var writer = Xml.XmlTextWriter.Create(stringWriter, settings);
      writer.Element("html");
      writer.Element("body");
      writer.Comment("start of output");
      writer.Element("div").Attribute("id", "test").Attribute("style", "margin:0px;");
      writer.Element("p").Attribute("style", "margin:0px;");
      writer.Value("some    text" + (char)160 + (char)160 + " & < > stuff");
      writer.ElementEnd();
      writer.Element("p", null);
      writer.Element("p", "another  line");
      writer.ElementEnd();
      writer.ElementEnd();
      writer.ElementEnd();
      writer.Flush();
      Debug.Print(stringWriter.ToString());
    }

    //declare @links table (
    //  line nvarchar(128) null,
    //  payroll int null
    //);

    //update ment
    //set PAYROLL_DEPARTMENT = pay.id
    //from innovator.MANUFACTURING_ENTITY ment
    //inner join (select distinct * from @links) lnk
    //on ment.ITEM_NUMBER = lnk.line
    //inner join innovator.PAYROLL_DEPARTMENT pay
    //on lnk.payroll = pay.MES_ID

    public static void GetLineData()
    {
      var wrWebRequest = (HttpWebRequest)(WebRequest.Create("http://orb/BI/Lines?showAll=True"));
      wrWebRequest.Credentials = new NetworkCredential("tltest", "ecmirror", "WONDERLAN");
      var hwrWebResponse = (HttpWebResponse)(wrWebRequest.GetResponse());
      var reader = new IO.StreamTextSource(hwrWebResponse.GetResponseStream())
        .Pipe(new Sgml.SgmlTextReader())
        .SkipTo(n => n.Name.LocalName == "table")
        .SkipTo(n => n.Name.LocalName == "tbody")
        .Pipe(new Data.HtmlTableReader());
      
      foreach (var row in reader.ProcessCells(ProcessLineDataCell))
      {
        //Debug.Print("{0}|{1}", row.ElementAt(0).Value, row.ElementAt(5).Value);
        if (row.ElementAt(5).Value != null && int.Parse(row.ElementAt(5).Value.ToString()) != -1)
        {
          Debug.Print("insert into @links values ('{0}',{1});", row.ElementAt(0).Value, row.ElementAt(5).Value);
        }
      }
    }
    public static void GetProductGroups()
    {
      var wrWebRequest = (HttpWebRequest)(WebRequest.Create("http://orb/BI/ProductGroups"));
      wrWebRequest.Credentials = new NetworkCredential("tltest", "ecmirror", "WONDERLAN");
      var hwrWebResponse = (HttpWebResponse)(wrWebRequest.GetResponse());
      var reader = new IO.StreamTextSource(hwrWebResponse.GetResponseStream())
        .Pipe(new Sgml.SgmlTextReader())
        .SkipTo(n => n.Name.LocalName == "table")
        .SkipTo(n => n.Name.LocalName == "tbody")
        .Pipe(new Data.HtmlTableReader());

      foreach (var row in reader.ProcessCells(ProcessProductGroupCell))
      {
        Debug.Print("<Item type=\"Product Group\" action=\"add\"><name>{0}</name><mes_id>{1}</mes_id></Item>", row.ElementAt(0).Value, row.ElementAt(5).Value);
      }
    }

    //declare @links table (
    //  payroll int null,
    //  product_group int null
    //);

    //update pay
    //set PRODUCT_GROUP = pg.id
    //from innovator.PAYROLL_DEPARTMENT pay
    //inner join @links lnk
    //on lnk.payroll = pay.MES_ID
    //inner join innovator.PRODUCT_GROUP pg
    //on lnk.product_group = pg.MES_ID

    public static void GetPayrollDepartments()
    {
      var wrWebRequest = (HttpWebRequest)(WebRequest.Create("http://orb/BI/PayrollDepartments?showAll=True"));
      wrWebRequest.Credentials = new NetworkCredential("tltest", "ecmirror", "WONDERLAN");
      var hwrWebResponse = (HttpWebResponse)(wrWebRequest.GetResponse());
      var reader = new IO.StreamTextSource(hwrWebResponse.GetResponseStream())
        .Pipe(new Sgml.SgmlTextReader())
        .SkipTo(n => n.Name.LocalName == "table")
        .SkipTo(n => n.Name.LocalName == "tbody")
        .Pipe(new Data.HtmlTableReader());

      foreach (var row in reader.ProcessCells(ProcessPayrollDepartmentCell))
      {
        //Debug.Print("{0}|{1}|{2}", row.ElementAt(0).Value, row.ElementAt(1).Value, row.ElementAt(8).Value);
        //Debug.Print("<Item type=\"Payroll Department\" action=\"merge\" where=\"mes_id={0}\"><mes_id>{0}</mes_id><name>{1}</name></Item>", row.ElementAt(0).Value, row.ElementAt(1).Value.ToString().Replace("&", "&amp;"));
        if (row.ElementAt(8).Value != null && ((string)row.ElementAt(8).Value) != "")
        {
          Debug.Print("insert into @links values ({0},{1});", row.ElementAt(0).Value, row.ElementAt(8).Value);
        }
      }
    }
    private static string ProcessLineDataCell(int i, IXmlNode node)
    {
      if (i == 5)
      {
        if (node.Name.LocalName == "a" && node.Type == XmlNodeType.Element) return node.Item("href").ToString().Substring(29);
        return null;
      }
      else
      {
        return (node.Value == null ? null : node.Value.ToString());
      }
    }
    private static string ProcessProductGroupCell(int i, IXmlNode node)
    {
      if (i == 5)
      {
        if (node.Name.LocalName == "a" && node.Type == XmlNodeType.Element) return node.Item("href").ToString().Substring(26);
        return null;
      }
      else
      {
        return (node.Value == null ? null : node.Value.ToString());
      }
    }
    private static string ProcessPayrollDepartmentCell(int i, IXmlNode node)
    {
      if (i == 8)
      {
        if (node.Name.LocalName == "a" && node.Type == XmlNodeType.Element)
        {
          var match = System.Text.RegularExpressions.Regex.Match(node.Item("href").ToString(), "pgrp=(\\d+)&");
          if (match.Success)
          {
            return match.Groups[1].Value;
          }
        }
        return null;
      }
      else
      {
        return (node.Value == null ? null : node.Value.ToString());
      }
    }
  }
}
